package anton.admin.schema;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;

@ManagedBean(name = "insertBean")
@SessionScoped
public class InsertBean {

    @Resource(name = "jdbc/antonsDatasource")
    private DataSource ds;

    private int shift_ID;
    private String shift_date;
    private String shift_time;
    private List<String> emp_unames;
    private List<Employee> all_emps;
    private String new_emp;
    private boolean updated;

    public void setShift(int shift_ID, String shift_date, String shift_time, List<String> emp_unames) {
        setShift_ID(shift_ID);
        setShift_date(shift_date);
        setShift_time(shift_time);
        setEmp_unames(emp_unames);
    }

    public int getShift_ID() {
        return shift_ID;
    }

    public void setShift_ID(int shift_ID) {
        this.shift_ID = shift_ID;
    }

    public String getShift_date() {
        return shift_date;
    }

    public void setShift_date(String shift_date) {
        this.shift_date = shift_date;
    }

    public String getShift_time() {
        return shift_time;
    }

    public void setShift_time(String shift_time) {
        this.shift_time = shift_time;
    }

    public List<String> getEmp_unames() {
        return emp_unames;
    }

    public void setEmp_unames(List<String> emp_unames) {
        this.emp_unames = emp_unames;
    }

    public List<Employee> getAll_emps() {
        return all_emps;
    }

    public void setAll_emps(List<Employee> all_emps) {
        this.all_emps = all_emps;
    }

    public String getNew_emp() {
        return new_emp;
    }

    public void setNew_emp(String new_emp) {
        this.new_emp = new_emp;
    }

    public boolean isUpdated() {
        return updated;
    }

    public void setUpdated(boolean updated) {
        this.updated = updated;
    }

    public String getEmpName(String uname) {
        String name = "";
        Iterator it = all_emps.iterator();
        while (it.hasNext()) {
            Employee tempEmp = (Employee) it.next();
            if (tempEmp.getUsername().equals(uname)) {
                name = tempEmp.getEmp_name();
            }
        }
        return name;
    }

    public void deleteEmp(String uname) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            if (ds == null) {
                throw new SQLException("Can't get data source");
            }

            con = ds.getConnection();

            if (con == null) {
                throw new SQLException("Can't get database connection");
            }

            ps = con.prepareStatement("DELETE FROM EmployeeShifts WHERE shift_ID=\"" + shift_ID + "\" AND username=\"" + uname + "\"");
            ps.execute();

            if (emp_unames.size() == 1) {
                ps = con.prepareStatement("DELETE FROM Shifts WHERE shift_ID=\"" + shift_ID + "\"");
                ps.execute();
            }
        } finally {
            closeAll(ps, con);
        }
        updated = true;
    }

    public void insertEmp() throws SQLException {
        if (!emp_unames.contains(new_emp)) {
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet result = null;

            try {
                if (ds == null) {
                    throw new SQLException("Can't get data source");
                }

                con = ds.getConnection();

                if (con == null) {
                    throw new SQLException("Can't get database connection");
                }

                if (emp_unames.isEmpty()) {
                    ps = con.prepareStatement("INSERT INTO Shifts (shift_date, shift_time) VALUES (\"" + shift_date + "\", \"" + shift_time + "\")");
                    ps.execute();
                    ps = con.prepareStatement("SELECT shift_ID FROM Shifts WHERE shift_date=\"" + shift_date + "\" AND shift_time=\"" + shift_time + "\"");
                    result = ps.executeQuery();
                    while (result.next()) {
                        shift_ID = result.getInt("shift_ID");
                    }

                }

                ps = con.prepareStatement("INSERT INTO EmployeeShifts (username, shift_ID) VALUES (\"" + new_emp + "\", \"" + shift_ID + "\")");
                ps.execute();

            } finally {
                closeAll(ps, con);
                if (result != null) {
                    try {
                        result.close();
                    } catch (SQLException e) {
                    }
                }
            }
            updated = true;
        }
    }

    private static void closeAll(PreparedStatement preState, Connection con) {
        if (preState != null) {
            try {
                preState.close();
            } catch (SQLException e) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
            }
        }
    }

    public InsertBean() {
        updated = false;
        shift_ID = 0;
    }

}
